﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;

namespace DAO
{
    public class ProductsDAO:DataProvider
    {
        public void Insert(ProductsDTO objDTO)
        {
            string strSQL = String.Format("Insert into Products(ProductName,CategoryID, QuantityPerUnit,UnitPrice,Image,Description,Discount) values('{0}',{1},'{2}',{3},'{4}','{5}',{6})",objDTO.ProductName,objDTO.CategoryID,objDTO.QualityPerUnit,objDTO.UnitPrice,objDTO.Image,objDTO.Description,objDTO.Discount);
            this.ExecuteNonQuery(strSQL);
        }
        public DataTable GetList()
        {
            string sql = "Select ProductID, ProductName,CategoryName, QuantityPerUnit, UnitPrice, Products.Image, Products.Description, Discount From Products, Categories Where Products.CategoryID= Categories.CategoryID";
            return this.ExecuteQuery(sql);
        }

        public bool Delete(int ProductID)
        {
            string sql = "Delete From Products Where ProductID = "+ ProductID;
            return this.ExecuteNonQuery(sql);

        }
        public void Update(ProductsDTO objDTO)
        {
            string sql = String.Format("Update Products set ProductName='{0}', CategoryID={1},QuantityPerUnit='{2}', UnitPrice={3}, Products.Image='{4}', Products.Description='{5}', Discount={6} Where ProductID={7}",objDTO.ProductName,objDTO.CategoryID,objDTO.QualityPerUnit,objDTO.UnitPrice,objDTO.Image,objDTO.Description,objDTO.Discount,objDTO.ProductID);
            this.ExecuteNonQuery(sql);
        }

        public DataTable GetProductByID(int ID)
        {
            string sql = "Select ProductID, ProductName,CategoryID, QuantityPerUnit, UnitPrice, Products.Image, Products.Description, Discount From Products Where ProductID=" + ID;
            return this.ExecuteQuery(sql);
        }
    }
}
